package dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import dao.FabriqueDAO;
import modele.Formateur;
import modele.Personne;
import modele.Session;

public class SessionDAO implements IDAO<Session> {

	static final String			SQL_INSERT				= "INSERT INTO session (id_session, id_formation, date_debut, date_fin, nb_max_places, tarif, date_debut_inscription, date_fin_inscription) VALUES (?, ?,?,?,?,?,?,?);";
	static final String			SQL_DELETE				= "DELETE FROM session WHERE session.id_session = ?";
	static final String			SQL_UPDATE				= "UPDATE session SET id_formation = ?, date_debut = ?, date_fin = ?, nb_max_places = ?, tarif = ?, date_debut_inscription = ? , date_fin_inscription = ? WHERE session.id_session = ?";
	static final String			SQL_SELECT_ITEM			= "SELECT * FROM session WHERE session.id_session = ?";
	static final String			SQL_SELECT_LIST			= " SELECT * FROM session";
	
	// Définition des variables en static.
	private static final String	ID_SESSION				= "id_session";
	private static final String	ID_FORMATION			= "id_formation";
	private static final String	DATE_DEBUT				= "date_debut";
	private static final String	DATE_FIN				= "date_fin";
	private static final String	NB_MAX_PLACES			= "nb_max_places";
	private static final String	TARIF					= "tarif";
	private static final String	DATE_DEBUT_INSCRIPTION	= "date_debut_inscription";
	private static final String	DATE_FIN_INSCRIPTION	= "date_fin_inscription";

	private Connection			connection;

	public SessionDAO() throws SQLException {
		connection = FabriqueDAO.getConnection();
	}

	public void close() throws SQLException {
		connection.close();
	}

	/**
	 * Sessions de la formation de id indiqué, commençant au plus
	 * tôt à la date indiquée.
	 * 
	 * @param idFormation
	 *            id de la formation
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<Session> listerSessionsFutures(Date dateDebut, int idFormation) throws SQLException {
		ArrayList<Session> tab = new ArrayList<Session>();
		String ls = "SELECT * FROM session WHERE date_debut >= ?";
		if (idFormation > 0) {
			ls += " AND id_formation = ? "; // le ls+ permet de répeter le ls en
											// ajoutant une partie.
		}
		ls += " ORDER BY date_debut ASC"; // ascendant
		PreparedStatement ordre = connection.prepareStatement(ls);
		ordre.setDate(1, dateDebut);
		if (idFormation > 0) {
			ordre.setInt(2, idFormation);
		}
		ResultSet res = ordre.executeQuery();
		while (res.next()) {
			int ids = res.getInt("id_session");
			int idf = res.getInt("id_formation");
			Date ddebut = res.getDate("date_debut");
			Date ddfin = res.getDate("date_fin");
			int nbmaxp = res.getInt("nb_max_places");
			double tarif = res.getDouble("tarif");
			Date ddinscri = res.getDate("date_debut_inscription");
			Date dfinscri = res.getDate("date_fin_inscription");
			Session agri = new Session(ids, idf, ddebut, ddfin, nbmaxp, tarif, ddinscri, dfinscri);
			tab.add(agri);
		}
		return tab;
	}

	/**
	 * Sessions commençant au plus tôt à la date indiquée,
	 * toutes formations confondues
	 */
	public List<Session> listerSessionsFutures(Date dateDebut) throws SQLException {
		return listerSessionsFutures(dateDebut, 0);
	}

	/**
	 * Sessions ouvertes à candidature, pour la formation de id indiqué, à la
	 * date donnée (c'est à dire quand la date donnée se trouve entre les dates
	 * de début et de fin d'inscription).
	 */
	public List<Session> listerSessionsOuvertes(int idFormation) throws SQLException {
		ArrayList<Session> tab = new ArrayList<Session>();
		try {
			String lso = "SELECT * FROM session WHERE date_debut_inscription <= now()  AND date_fin_inscription >= now()";

			// String lso = "SELECT * FROM session WHERE date_debut_inscription <= now()  AND date_fin_inscription >= now()";
			/*
			 * date_debut_ins < now and debut ins > (now - 90) and date_debut_formation < now 
			 */

			if (idFormation > 0) {
				lso += " AND id_formation = ? ";
			}
			PreparedStatement ordre = connection.prepareStatement(lso);
			// ordre.setInt(1, idFormation);
			ResultSet res = ordre.executeQuery();
			while (res.next()) {
				int ids = res.getInt("id_session");
				int idf = res.getInt("id_formation");
				Date ddebut = res.getDate("date_debut");
				Date ddfin = res.getDate("date_fin");
				int nbmaxp = res.getInt("nb_max_places");
				double tarif = res.getDouble("tarif");
				Date ddinscri = res.getDate("date_debut_inscription");
				Date dfinscri = res.getDate("date_fin_inscription");
				Session agri = new Session(ids, idf, ddebut, ddfin, nbmaxp, tarif, ddinscri, dfinscri);
				tab.add(agri);
			}
		} catch (SQLException e1) {
			System.out.println("non" + e1.getMessage());
		}
		return tab;
	}

	/**
	 * Sessions ouvertes, toutes formations confondues. (c'est à dire quand la
	 * date donnée se trouve entre les dates de début et de fin d'inscription).
	 * 
	 * @throws SQLException
	 */
	public List<Session> listerSessionsOuvertes() throws SQLException {
		return listerSessionsOuvertes(0);
	}

	public void inserer(Session s) throws SQLException {
		// Le Return Generated KEys sert pour l'autogéneration des id.
		PreparedStatement pt = connection.prepareStatement(SQL_INSERT, Statement.RETURN_GENERATED_KEYS);
		//
		pt.setInt(1, s.getIdSession());
		pt.setInt(2, s.getIdFormation());
		pt.setDate(3, s.getDateDebut());
		pt.setDate(4, s.getDateFin());
		pt.setInt(5, s.getNbMaxPlaces());
		pt.setDouble(6, s.getTarif());
		pt.setDate(7, s.getDateDebutInscription());
		pt.setDate(8, s.getDateFinInscription());
		pt.executeUpdate();
		ResultSet rs = pt.getGeneratedKeys();
		if (rs.next()) {
			s.setIdSession(rs.getInt(1));
		}
		pt.close();
	}

	public Session lireParId(int id) throws SQLException {
		Session s = null;
		PreparedStatement pt = connection.prepareStatement(SQL_SELECT_ITEM);
		pt.setInt(1, id);
		ResultSet rs = pt.executeQuery();
		if (rs.next()) {
			s = new Session();
			s.setIdSession(rs.getInt(ID_SESSION));
			s.setIdFormation(rs.getInt(ID_FORMATION));
			s.setDateDebut(rs.getDate(DATE_DEBUT));
			s.setDateFin(rs.getDate(DATE_FIN));
			s.setNbMaxPlaces(rs.getInt(NB_MAX_PLACES));
			s.setTarif(rs.getDouble(TARIF));
			s.setDateDebutInscription(rs.getDate(DATE_DEBUT_INSCRIPTION));
			s.setDateFinInscription(rs.getDate(DATE_FIN_INSCRIPTION));
		}
		rs.close();
		pt.close();
		return s;
	}

	public void supprimer(int id) throws SQLException {
		PreparedStatement pt = connection.prepareStatement(SQL_DELETE);
		pt.setInt(1, id);
		pt.executeUpdate();
		pt.close();
	}

	@Override
	public List<Session> lister() throws SQLException {
		ArrayList<Session> tableauSession = new ArrayList<>();
		PreparedStatement pt = connection.prepareStatement(SQL_SELECT_LIST);
		ResultSet rs = pt.executeQuery();
		while(rs.next()){
			Session s = new Session();
			s.setIdSession(rs.getInt(ID_SESSION));
			s.setIdFormation(rs.getInt(ID_FORMATION));
			s.setDateDebut(rs.getDate(DATE_DEBUT));
			s.setDateFin(rs.getDate(DATE_FIN));
			s.setNbMaxPlaces(rs.getInt(NB_MAX_PLACES));
			s.setTarif(rs.getDouble(TARIF));
			s.setDateDebutInscription(rs.getDate(DATE_DEBUT_INSCRIPTION));
			s.setDateFinInscription(rs.getDate(DATE_FIN_INSCRIPTION));
			tableauSession.add(s);
		}
	return tableauSession;
	}

	
	
	/*public void modifier(Session s) throws SQLException {
		PreparedStatement pt = connection
				.prepareStatement(SQL_UPDATE);
		pt.setInt(1, s.getIdSession());
		pt.setInt(2, s.getIdFormation());
		pt.setDate(3, s.getDateDebut());
		pt.setDate(4, s.getDateFin());
		pt.setInt(5, s.getNbMaxPlaces());
		pt.setDouble(6, s.getTarif());
		pt.setDate(7, s.getDateDebutInscription());
		pt.setDate(8, s.getDateFinInscription());
		pt.executeUpdate();
		}
	*/	

	

	public ArrayList<Session> listerParFormation(int idFormation) throws SQLException {
		ArrayList<Session> sessions = new ArrayList<>();

		PreparedStatement pt = connection.prepareStatement("SELECT * FROM session WHERE session.id_formation = ?");
		pt.setInt(1, idFormation);
		ResultSet rs = pt.executeQuery();

		while (rs.next()) {
			Session s = new Session();
			s.setIdSession(rs.getInt(ID_SESSION));
			s.setIdFormation(rs.getInt(ID_FORMATION));
			s.setDateDebut(rs.getDate(DATE_DEBUT));
			s.setDateFin(rs.getDate(DATE_FIN));
			s.setNbMaxPlaces(rs.getInt(NB_MAX_PLACES));
			s.setTarif(rs.getDouble(TARIF));
			s.setDateDebutInscription(rs.getDate(DATE_DEBUT_INSCRIPTION));
			s.setDateFinInscription(rs.getDate(DATE_FIN_INSCRIPTION));

			sessions.add(s);
		}

		rs.close();
		pt.close();

		return sessions;
	}

	public List<Session> listerSessionsOuvertesRecherche(int idFormation) throws SQLException {

		ArrayList<Session> tab = new ArrayList<Session>();
		try {
			// String lso =
			// "SELECT * FROM session WHERE date_debut_inscription >= (NOW() - INTERVAL 45 DAY)  AND now() <= date_fin_inscription  and date_debut > now()";
			String lso = "SELECT * FROM session WHERE (now() between date_debut_inscription and date_fin_inscription)  and date_debut > now()";

			if (idFormation > 0) {
				lso += " AND id_formation = ? ";
			}

			PreparedStatement ordre = connection.prepareStatement(lso);

			if (idFormation > 0) {
				ordre.setInt(1, idFormation);
			}

			ResultSet res = ordre.executeQuery();
			while (res.next()) {
				int ids = res.getInt("id_session");
				int idf = res.getInt("id_formation");
				Date ddebut = res.getDate("date_debut");
				Date ddfin = res.getDate("date_fin");
				int nbmaxp = res.getInt("nb_max_places");
				double tarif = res.getDouble("tarif");
				Date ddinscri = res.getDate("date_debut_inscription");
				Date dfinscri = res.getDate("date_fin_inscription");
				Session agri = new Session(ids, idf, ddebut, ddfin, nbmaxp, tarif, ddinscri, dfinscri);
				tab.add(agri);
			}
		} catch (SQLException e1) {
			System.out.println("non" + e1.getMessage());
		}
		return tab;
	}

	public List<Session> listerSessionsFuturesRecherche(int idFormation) throws SQLException {

		ArrayList<Session> tab = new ArrayList<Session>();
		try {
			String lso = "SELECT * FROM session WHERE date_debut_inscription > NOW()";

			if (idFormation > 0) {
				lso += " AND id_formation = ? ";
			}

			PreparedStatement ordre = connection.prepareStatement(lso);

			if (idFormation > 0) {
				ordre.setInt(1, idFormation);
			}

			ResultSet res = ordre.executeQuery();
			while (res.next()) {
				int ids = res.getInt("id_session");
				int idf = res.getInt("id_formation");
				Date ddebut = res.getDate("date_debut");
				Date ddfin = res.getDate("date_fin");
				int nbmaxp = res.getInt("nb_max_places");
				double tarif = res.getDouble("tarif");
				Date ddinscri = res.getDate("date_debut_inscription");
				Date dfinscri = res.getDate("date_fin_inscription");
				Session agri = new Session(ids, idf, ddebut, ddfin, nbmaxp, tarif, ddinscri, dfinscri);
				tab.add(agri);
			}
		} catch (SQLException e1) {
			System.out.println("non" + e1.getMessage());
		}
		return tab;
	}

	@Override
	public void modifier(Session element) throws SQLException {
		// TODO Auto-generated method stub
		
	}
}